﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ProcCodeCreator
{
    public class Common
    {
        public static Dictionary<string, string> DictDBType = new Dictionary<string, string>() { 
            {"bigint","BigInt"},
            {"bit","Bit"},
            {"char","Char"},
            {"datetime","DateTime"},
            {"decimal","Decimal"},
            {"float","Float"},
            {"int","Int"},
            {"nchar","NChar"},
            {"ntext","NText"},
            {"nvarchar","NVarChar"},
            {"smallint","SmallInt"},
            {"text","Text"},
            {"tinyint","TinyInt"},
            {"varchar","VarChar"},
            {"date","Date"}
        };

        public static Dictionary<string, string> Dict = new Dictionary<string, string>();

        static Common()
        {
            Dict.Add("date", "DateTime");
            Dict.Add("tinyint", "int");
            Dict.Add("smallint", "int");
            Dict.Add("int", "int");
            Dict.Add("datetime", "DateTime");
            Dict.Add("float", "float");
            Dict.Add("ntext", "string");
            Dict.Add("text", "string");
            Dict.Add("bit", "int");
            Dict.Add("decimal", "decimal");
            Dict.Add("bigint", "long");
            Dict.Add("varchar", "string");
            Dict.Add("char", "char");
            Dict.Add("nvarchar", "string");
            Dict.Add("nchar", "string");
        }

        public static string GetTypeVal(string Key)
        {
            string val = string.Empty;
            Dict.TryGetValue(Key, out val);
            return val;
        }

        public static string GetDBType(string Key)
        {
            string val = string.Empty;
            DictDBType.TryGetValue(Key, out val);
            return val;
        }

        internal static string GetProce(List<Param> pList, string pName)
        {
            StringBuilder sb = new StringBuilder();
            string param = string.Empty;
            sb.AppendFormat("public static int {0}(", pName);
            foreach (Param p in pList)
            {
                param += p.OutType + " " + p.Type + " " + p.Name + ",";
            }
            if (param.Length > 0)
            {
                param = param.Substring(0, param.Length - 1);
            }
            sb.Append(param);
            sb.Append(")");

            sb.AppendLine("{");

            sb.AppendLine("   SqlParameter[] parameters = {");
            string ParaStr = string.Empty;
            foreach (Param p in pList)
            {
                ParaStr += "        new SqlParameter(\"@" + p.Name + "\", SqlDbType." + Common.GetDBType(p.DBType) + "),\r\n";
            }
            if (ParaStr.Length > 0)
            {
                ParaStr = ParaStr.Substring(0, ParaStr.Length - 1);
            }
            sb.AppendFormat(ParaStr);
            sb.AppendLine("};");

            for (int i = 0; i < pList.Count; i++)
            {
                if (pList[i].OutType == "out")
                {
                    sb.AppendLine("    parameters[" + i.ToString() + "].Direction = ParameterDirection.Output;");
                }
                else
                {
                    sb.AppendLine("    parameters[" + i.ToString() + "].Value = " + pList[i].Name + ";");
                }
            }

            sb.AppendLine("    SqlDataAccess sqlDataAccess = SqlDataAccess.CreateDataAccess();");
            sb.AppendLine("    int result = sqlDataAccess.ExecuteNonQuery(\"" + pName + "\", parameters);");

            for (int i = 0; i < pList.Count; i++)
            {
                if (pList[i].OutType == "out")
                {
                    sb.AppendLine("    " + pList[i].Name + " = parameters[" + i.ToString() + "].Value == DBNull.Value ? default(" + pList[i].Type + ") :  (" + pList[i].Type + ")parameters[" + i.ToString() + "].Value;");

                }
            }
            sb.AppendLine("    return result;");
            sb.AppendLine("}");
            return sb.ToString();
        }

        internal static string GetProceBLL(List<Param> pList, string pName)
        {
            StringBuilder sb = new StringBuilder();
            string param = string.Empty;
            sb.AppendFormat("public static {0}(", pName);
            foreach (Param p in pList)
            {
                param += p.OutType + " " + p.Type + " " + p.Name + ",";
            }
            if (param.Length > 0)
            {
                param = param.Substring(0, param.Length - 1);
            }
            sb.Append(param);
            sb.Append(")");

            sb.AppendLine("{");

            sb.AppendLine("   SqlParameter[] parameters = {");
            string ParaStr = string.Empty;
            foreach (Param p in pList)
            {
                ParaStr += "        new SqlParameter(\"@" + p.Name + "\", SqlDbType." + Common.GetDBType(p.DBType) + "),\r\n";
            }
            if (ParaStr.Length > 0)
            {
                ParaStr = ParaStr.Substring(0, ParaStr.Length - 1);
            }
            sb.AppendFormat(ParaStr);
            sb.AppendLine("};");

            for (int i = 0; i < pList.Count; i++)
            {
                if (pList[i].OutType == "out")
                {
                    sb.AppendLine("    parameters[" + i.ToString() + "].Value = ParameterDirection.Output;");
                }
                else
                {
                    sb.AppendLine("    parameters[" + i.ToString() + "].Value = " + pList[i].Name + ";");
                }
            }

            sb.AppendLine("    SqlDataAccess sqlDataAccess = SqlDataAccess.CreateDataAccess();");
            sb.AppendLine("    int result = sqlDataAccess.ExecuteNonQuery(\"" + pName + "\", parameters);");

            for (int i = 0; i < pList.Count; i++)
            {
                if (pList[i].OutType == "out")
                {
                    sb.AppendLine("    " + pList[i].Name + " = (" + pList[i].Type + ")parameters[" + i.ToString() + "].Value;");

                }
            }
            sb.AppendLine("    return result;");
            sb.AppendLine("}");
            return sb.ToString();
        }

        internal static string GetProceModel(List<RetC> llist, string PName)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendLine("using System;");
            sb.AppendLine("using System.Collections.Generic;");
            sb.AppendLine("using System.Linq;");
            sb.AppendLine("using System.Text;");
            sb.AppendLine("using System.Threading.Tasks;");
            sb.AppendLine("");
            sb.AppendLine("namespace REL.Microsoft.KPSoft.Entity");
            sb.AppendLine("{");
            sb.AppendLine("    [Serializable]");
            sb.AppendLine("    public class " + PName + "_Result");
            sb.AppendLine("    {");
            foreach (RetC c in llist)
            {
                sb.AppendLine("         public " + c.Type + " " + c.Name + " { get; set; }");
            }
            sb.AppendLine("    }");
            sb.AppendLine("}");
            return sb.ToString();
        }

        internal static string GetProceForModel(List<Param> pList, string pName)
        {
            StringBuilder sb = new StringBuilder();
            string param = string.Empty;
            sb.AppendFormat("public static List<" + pName + "_Result> {0}(", pName);
            foreach (Param p in pList)
            {
                param += p.OutType + " " + p.Type + " " + p.Name + ",";
            }
            if (param.Length > 0)
            {
                param = param.Substring(0, param.Length - 1);
            }
            sb.Append(param);
            sb.Append(")");

            sb.AppendLine("{");

            sb.AppendLine("   SqlParameter[] parameters = {");
            string ParaStr = string.Empty;
            foreach (Param p in pList)
            {
                ParaStr += "        new SqlParameter(\"@" + p.Name + "\", SqlDbType." + Common.GetDBType(p.DBType) + "),\r\n";
            }
            if (ParaStr.Length > 0)
            {
                ParaStr = ParaStr.Substring(0, ParaStr.Length - 1);
            }
            sb.AppendFormat(ParaStr);
            sb.AppendLine("};");

            for (int i = 0; i < pList.Count; i++)
            {
                if (pList[i].OutType == "out")
                {
                    sb.AppendLine("    parameters[" + i.ToString() + "].Direction = ParameterDirection.Output;");
                }
                else
                {
                    sb.AppendLine("    parameters[" + i.ToString() + "].Value = " + pList[i].Name + ";");
                }
            }
            sb.AppendLine("    SqlDataAccess sqlDataAccess = SqlDataAccess.CreateDataAccess();");
            sb.AppendLine("    DataSet result = sqlDataAccess.ExecuteDataSet(\"" + pName + "\", parameters);");

            for (int i = 0; i < pList.Count; i++)
            {
                if (pList[i].OutType == "out")
                {
                    sb.AppendLine("    " + pList[i].Name + " = parameters[" + i.ToString() + "].Value == DBNull.Value ? default(" + pList[i].Type + ") :  (" + pList[i].Type + ")parameters[" + i.ToString() + "].Value;");

                }
            }

            sb.AppendLine("    List<" + pName + "_Result> list = REL.Microsoft.KPSoft.Common.Utility.ConvertToList<" + pName + "_Result>(result.Tables[0]);");

            sb.AppendLine("    return list;");
            sb.AppendLine("}");
            return sb.ToString();
        }
    }
}
